package pos.datos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import pos.datos.*;
import pos.dominio.*;

public class JDBCReservaDAO implements IReservaDAO {



	public void borrarReserva(String IDReserva) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		String sql = "DELETE FROM reservas WHERE (IDReserva = ?) ";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, IDReserva);
			stmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
	}

	public void insertaReserva(Reserva reserva) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		String sql = "INSERT INTO reservas ( IDReserva, IDUsuario, IDZona, cod_Transporte, IDHorario, Fecha) VALUES (?, ?, ?, ?, ?, ?) ";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, reserva.getIDReserva());
			stmt.setString(2, reserva.getIdUsuario());
			stmt.setString(3, reserva.getIdZona());
			stmt.setString(4, reserva.getIdVehiculo());
			stmt.setString(5, reserva.getHora());
			stmt.setDate(6, reserva.getFecha());
			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

	}

	
	public List devolverListaReservas() {
		Connection conn = ConnectionManager.getInstance().checkOut();
        PreparedStatement stmt = null;
        List searchResults = new LinkedList();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM reservas";
            stmt = conn.prepareStatement(sql); 
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Reserva temp =new Reserva ();
                temp.setIDReserva(result.getString("IDReserva"));
                temp.setIdUsuario(result.getString("IDUsuario"));
                temp.setIdZona(result.getString("IDZona"));
                temp.setIdVehiculo(result.getString("cod_Transporte"));
                temp.setFecha(result.getDate("Fecha"));
                temp.setHora(result.getString("IDHorario"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
	}

	@Override
	public List devolverListaPorUsuario(String IDUsuario) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        List searchResults = new LinkedList();
        ResultSet result = null;
        
        try {

            String sql = "SELECT * FROM reservas WHERE ( IDUsuario = ? ) AND (Fecha = ?)";
            stmt = conn.prepareStatement(sql); 
            stmt.setString(1, IDUsuario);
            java.util.Date fechaHoy = new java.util.Date();
    		long hoyMilisegundos=fechaHoy.getTime();     
    		java.sql.Date sqlHoy=new java.sql.Date(hoyMilisegundos);
            stmt.setDate(2, sqlHoy);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Reserva temp =new Reserva ();
                temp.setIDReserva(result.getString("IDReserva"));
                temp.setIdUsuario(result.getString("IDUsuario"));
                temp.setIdZona(result.getString("IDZona"));
                temp.setIdVehiculo(result.getString("cod_Transporte"));
                temp.setFecha(result.getDate("Fecha"));
                temp.setHora(result.getString("IDHorario"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
	}

	public Reserva devolverReserva(String IDReserva) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
     
        ResultSet result = null;
        
        Reserva temp=new Reserva();
        try {

            String sql = "SELECT * FROM reservas WHERE ( IDReserva = ? )";
            stmt = conn.prepareStatement(sql); 
            stmt.setString(1, IDReserva);
            stmt.executeQuery();
            result = stmt.executeQuery();
           
            result.next();
               
                temp.setIDReserva(result.getString("IDReserva"));
                temp.setIdUsuario(result.getString("IDUsuario"));
                temp.setIdZona(result.getString("IDZona"));
                temp.setIdVehiculo(result.getString("cod_Transporte"));
                temp.setFecha(result.getDate("Fecha"));
                temp.setHora(result.getString("IDHorario"));
            
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return temp;
	}

public List devolverListaUsuarioCompleta(String IDUsuario) {
	Connection conn = ConnectionManager.getInstance().checkOut();
	PreparedStatement stmt = null;
    List searchResults = new LinkedList();
    ResultSet result = null;
    
    try {

        String sql = "SELECT * FROM reservas WHERE ( IDUsuario = ? )";
        stmt = conn.prepareStatement(sql); 
        stmt.setString(1, IDUsuario);
        stmt.executeQuery();
        result = stmt.executeQuery();

        while (result.next()) {
            Reserva temp =new Reserva ();
            temp.setIDReserva(result.getString("IDReserva"));
            temp.setIdUsuario(result.getString("IDUsuario"));
            temp.setIdZona(result.getString("IDZona"));
            temp.setIdVehiculo(result.getString("cod_Transporte"));
            temp.setFecha(result.getDate("Fecha"));
            temp.setHora(result.getString("IDHorario"));
            searchResults.add(temp);
        }
    } catch (SQLException e) {
        System.out.println("Message: " + e.getMessage());
        System.out.println("SQLState: " + e.getSQLState());
        System.out.println("ErrorCode: " + e.getErrorCode());
    } finally {
        ConnectionManager.getInstance().checkIn(conn);
        try {
            if (result != null)
                result.close();
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
        }

    }
    return searchResults;
}
}
